<?php

require '../../FormWriter.php';
require '../../PhpTest.php';

/*
  Testing samples of implemented queries

  TODO: when PHP 5.3 will be out, nowdocs should be used instead of heredocs.
  They do not require escaping when embedding PHP code in the PhpTest::test 
  calls.
 */

$company = new Company();
echo '<pre>';
$pre = 'global $company;
return $company->convert_qbe(';
$post = ');';
PhpTest::verbose('body error passed');


# Schema queries:

PhpTest::test('qbe_convert-Company.1', 'List the available table names in the Company database', <<<EOT
$pre
  'P.'
$post
EOT
, 'SHOW TABLES');

PhpTest::test('qbe_convert-Company.2', 'Find the column headings of the TYPE table', <<<EOT
$pre
  'type P.'

$post
EOT
, 'SHOW COLUMNS FROM `type`');


# Selection queries:

PhpTest::test('qbe_convert-Company.3', 'Display of green items', <<<EOT
$pre
  array(
    'type' => array(
      'item'  => 'P._ROD',
      'color' => 'green'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`item` FROM `type` T1 WHERE T1.`color`='green'");

PhpTest::test('qbe_convert-Company.4', 'Alternate formulation for qbe_convert-Company.3 withi a stand-alone P.', <<<EOT
$pre
  array(
    'type' => array(
      'item'  => 'P.',
      'color' => 'green'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`item` FROM `type` T1 WHERE T1.`color`='green'");

PhpTest::test('qbe_convert-Company.5', 'Print out all colors', <<<EOT
$pre
  array(
    'type' => array(
      'color'  => 'P._WHITE'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`color` FROM `type` T1');

PhpTest::test('qbe_convert-Company.6', 'Print out all colors in alphabetical order', <<<EOT
$pre
  array(
    'type' => array(
      'color'  => 'P.AO._RED'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`color` FROM `type` T1 ORDER BY T1.`color`');

PhpTest::test('qbe_convert-Company.7', 'Print out the entire TYPE table', <<<EOT
$pre
	array(
		'type' => array(
			'item'  => 'P.',
			'color' => 'P.',
			'size'  => 'P.'
		)
	)
$post
EOT
, 'SELECT DISTINCT T1.`item`,T1.`color`,T1.`size` FROM `type` T1');

PhpTest::test('qbe_convert-Company.8', 'A shorthand representation of query qbe_convert-Company.7. Here the print operator P. is applied against the entire row', <<<EOT
$pre
	array(
		'type' => array(
			'.rel'  => 'P.'
		)
	)
$post
EOT
, 'SELECT DISTINCT T1.* FROM `type` T1');

PhpTest::test('qbe_convert-Company.9', 'Print the names of the employees who work in the toy department and earn more than $10000', <<<EOT
$pre
	array(
		'emp' => array(
			'name' => 'P.',
			'sal'  => '>10000',
			'dept' => 'TOY'
		)
	)
$post
EOT
, "SELECT DISTINCT T1.`name` FROM `emp` T1 WHERE T1.`sal`>10000 AND T1.`dept`='TOY'");

PhpTest::test('qbe_convert-Company.10', ' Print the green items that start with the letter I', <<<EOT
$pre
  array(
    'type' => array(
      'item'  => 'P.I_KE',
      'color' => 'green'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`item` FROM `type` T1 WHERE T1.`item` LIKE 'I%' AND T1.`color`='green'");

PhpTest::test('qbe_convert-Company.11', 'Print all the green items sold by the toy department', <<<EOT
$pre
  array(
    'type' => array(
      'item'  => 'P._NUT',
      'color' => 'green'
    ),
    'sales' => array(
      'dept' => 'TOY',
      'item' => '_NUT'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`item` FROM `type` T1 JOIN `sales` T2 ON T1.`color`='green' AND T2.`dept`='TOY' AND T1.`item`=T2.`item`");

PhpTest::test('qbe_convert-Company.12', 'Find the names and salaries of the employees who earn more than Lewis', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('P.', 'LEWIS'),
      'sal'  => array('P.>_S1', '_S1')
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`name`,T1.`sal` FROM `emp` T1 JOIN `emp` T2 ON T2.`name`='LEWIS' AND T1.`sal`>T2.`sal`");

PhpTest::test('qbe_convert-Company.13', 'Same as qbe_convert-Company.12 but with the order of the rows inverted', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('LEWIS', 'P.'),
      'sal'  => array('_S1', 'P.>_S1')
    )
  )
$post
EOT
, "SELECT DISTINCT T2.`name`,T2.`sal` FROM `emp` T1 JOIN `emp` T2 ON T1.`name`='LEWIS' AND T2.`sal`>T1.`sal`");

PhpTest::test('qbe_convert-Company.14', ' Find the names and salaries of the employees who earn more than Lewis and work in a department thatsells pens', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('P.', 'LEWIS'),
      'dept' => '_TOY',
      'sal'  => array('P.>_S1', '_S1')
    ),
    'sales' => array(
      'dept' => '_TOY',
      'item' => 'PEN'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`name`,T1.`sal` FROM `emp` T1 JOIN `sales` T2 ON T1.`dept`=T2.`dept` AND T2.`item`='PEN' JOIN `emp` T3 ON T3.`name`='LEWIS' AND T1.`sal`>T3.`sal`");

PhpTest::test('qbe_convert-Company.15', 'Find the names of the employees who earn more than their managers', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('P.', '_JONES'),
      'sal'  => array('>_S1', '_S1'),
      'mgr'  => '_JONES'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`name` FROM `emp` T1 JOIN `emp` T2 ON T1.`mgr`=T2.`name` AND T1.`sal`>T2.`sal`');

PhpTest::test('qbe_convert-Company.16', 'Print the departments that sell an item not supplied by the
Pencraft Company', <<<EOT
$pre
  array(
    'sales' => array(
      'dept' => 'P.',
      'item' => '_INK'
    ),
    'supply' => array(
      '.rel'     => '!',
      'item'     => '_INK',
      'supplier' => 'PENCRAFT'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`dept` FROM `sales` T1 LEFT JOIN `supply` T2 ON T1.`item`=T2.`item` AND T2.`supplier`='PENCRAFT' WHERE T2.`item` IS NULL");

PhpTest::test('qbe_convert-Company.17', 'Print the departments that sell items subplied by a supplier other than the Pencraft Company', <<<EOT
$pre
  array(
    'sales' => array(
      'dept' => 'P.',
      'item' => '_INK'
    ),
    'supply' => array(
      'item'     => '_INK',
      'supplier' => '!PENCRAFT'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`dept` FROM `sales` T1 JOIN `supply` T2 ON T1.`item`=T2.`item` AND T2.`supplier`!='PENCRAFT'");

PhpTest::test('qbe_convert-Company.18', 'Print out each department with its corresponding suppliers', <<<EOT
$pre
  array(
    'zzz' => array(
      'thing' => 'P._TOY',
      'xxx'   => 'P._IBM'
    ),
    'sales' => array(
      'dept' => '_TOY',
      'item' => '_INK'
    ),
    'supply' => array(
      'item'     => '_INK',
      'supplier' => '_IBM'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`dept` AS `thing`,T2.`supplier` AS `xxx` FROM `sales` T1 JOIN `supply` T2 ON T1.`item`=T2.`item`');

PhpTest::test('qbe_convert-Company.19', 'Same as qbe_convert-Company.18 but without requiring the user to create a new table for output only', <<<EOT
$pre
  array(
    'sales' => array(
      'dept' => 'P._TOY',
      'item' => '_INK'
    ),
    'supply' => array(
      'item'     => '_INK',
      'supplier' => 'P._IBM'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`dept`,T2.`supplier` FROM `sales` T1 JOIN `supply` T2 ON T1.`item`=T2.`item`');

PhpTest::test('qbe_convert-Company.20', 'For each employee, list name and salary plus commission', <<<EOT
$pre
  array(
    'output' => array(
      'name'     => 'P._JONES',
      'earnings' => 'P.(_S1 + _S2)'
    ),
    'emp1' => array(
      'name'       => '_JONES',
      'sal'        => '_S1',
      'commission' => '_S2'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`name` AS `name`,(T1.`sal` + T1.`commission`) AS `earnings` FROM `emp1` T1');

PhpTest::test('qbe_convert-Company.21', 'Get the names of the employees whose salaries are greater than the sum of those of Jones and Nelson', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('P.', 'JONES', 'NELSON'),
      'sal'  => array('_S1', '_S2', '_S3')
    ),
    '.cond' => '_S1 > (_S2 + _S3)'
  )
$post
EOT
, "SELECT DISTINCT T1.`name` FROM `emp` T1 JOIN `emp` T2 ON T2.`name`='JONES' JOIN `emp` T3 ON T3.`name`='NELSON' WHERE T1.`sal` > (T2.`sal` + T3.`sal`)");

PhpTest::test('qbe_convert-Company.22', 'Same as qbe_convert-Company.21 but without using a condition box', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => array('P.', 'JONES', 'NELSON'),
      'sal'  => array('>(_S2 + _S3)', '_S2', '_S3')
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`name` FROM `emp` T1 JOIN `emp` T2 ON T2.`name`='JONES' JOIN `emp` T3 ON T3.`name`='NELSON' WHERE T1.`sal`>(T2.`sal` + T3.`sal`)");

PhpTest::test('qbe_convert-Company.23', 'Print the names of employees whose salary is between $10 000 and $15 000, provided it is not $13 000', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => 'P.',
      'sal'  => '_S1',
    ),
    '.cond' => '_S1 > 10000 AND _S1 < 15000 AND _S1 != 13000'
  )
$post
EOT
, 'SELECT DISTINCT T1.`name` FROM `emp` T1 WHERE T1.`sal` > 10000 AND T1.`sal` < 15000 AND T1.`sal` != 13000');

PhpTest::test('qbe_convert-Company.24', 'Print the names of employees whose salary is either $10 000 or $13 000 or $16 000', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => 'P.',
      'sal'  => '_S1',
    ),
    '.cond' => '_S1 IN (10000, 13000, 16000)'
  )
$post
EOT
, 'SELECT DISTINCT T1.`name` FROM `emp` T1 WHERE T1.`sal` IN (10000, 13000, 16000)');

PhpTest::test('qbe_convert-Company.25', 'Count the total number employees', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => 'P.CNT.ALL._JONES'
    )
  )
$post
EOT
, 'SELECT DISTINCT COUNT(T1.`name`) FROM `emp` T1');

PhpTest::test('qbe_convert-Company.26', 'Count the total number of departments in the SALES table', <<<EOT
$pre
  array(
    'sales' => array(
      'dept' => 'P.CNT.UN.ALL._TOY'
    )
  )
$post
EOT
, 'SELECT DISTINCT COUNT(DISTINCT T1.`dept`) FROM `sales` T1');

PhpTest::test('qbe_convert-Company.27', 'Print the sum of the salaries in theToy Department', <<<EOT
$pre
  array(
    'emp' => array(
      'sal'  => 'P.SUM.ALL._S1',
      'dept' => 'TOY'
    )
  )
$post
EOT
, "SELECT DISTINCT SUM(T1.`sal`) FROM `emp` T1 WHERE T1.`dept`='TOY'");

PhpTest::test('qbe_convert-Company.28', 'Same as qbe_convert-Company.25 but uses an output table for the purpose of column renaming', <<<EOT
$pre
  array(
    'output' => array(
      'sum' => 'P.SUM.ALL._S1',
    ),
    'emp' => array(
      'sal'  => '_S1',
      'dept' => 'TOY'
    )
  )
$post
EOT
, "SELECT DISTINCT SUM(T1.`sal`) AS `sum` FROM `emp` T1 WHERE T1.`dept`='TOY'");

PhpTest::test('qbe_convert-Company.29', "For each department, print the name and the sum of the employees' salaries", <<<EOT
$pre
  array(
    'emp' => array(
      'sal'  => 'P.SUM.ALL._S1',
      'dept' => 'P.__TOY'
    )
  )
$post
EOT
, 'SELECT DISTINCT SUM(T1.`sal`),T1.`dept` FROM `emp` T1 GROUP BY T1.`dept`');

PhpTest::test('qbe_convert-Company.30', 'Get the departments that have more than three employees', <<<EOT
$pre
  array(
    'emp' => array(
      'name' => 'ALL._JONES',
      'dept' => 'P.__TOY'
    ),
    '.cond' => 'CNT.ALL._JONES > 3'
  )
$post
EOT
, 'SELECT DISTINCT T1.`dept` FROM `emp` T1 GROUP BY T1.`dept` HAVING COUNT(T1.`name`) > 3');


# Insertion queries

PhpTest::test('qbe_convert-Company.31', 'Insert into the employee table a new employee of the Toy Department named Jones, whose salary is $10 000, and whose manager is Henry', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'I.',
      'name' => 'JONES',
      'sal'  => 10000,
      'mgr'  => 'HENRY',
      'dept' => 'TOY'
    )
  )
$post
EOT
, "INSERT INTO `emp` (`name`,`sal`,`mgr`,`dept`) VALUES('JONES',10000,'HENRY','TOY')");

PhpTest::test('qbe_convert-Company.32', "Insert into the employee table an employee in the Toy Department whose name is Henry, whose manager's name is Lee, and whose salary is the same as that of Lewis", <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'I.',
      'name' => array('HENRY', 'LEWIS'),
      'sal'  => array('_S1', '_S1'),
      'mgr'  => 'LEE',
      'dept' => 'TOY'
    )
  )
$post
EOT
, "INSERT INTO `emp` (`name`,`sal`,`mgr`,`dept`) SELECT DISTINCT 'HENRY',T1.`sal`,'LEE','TOY' FROM `emp` T1 WHERE T1.`name`='LEWIS'");

PhpTest::test('qbe_convert-Company.33', 'Same as qbe_convert-Company.32 but with the order of the rows reversed', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => array('','I.'),
      'name' => array('LEWIS', 'HENRY'),
      'sal'  => array('_S1', '_S1'),
      'mgr'  => array('', 'LEE'),
      'dept' => array('', 'TOY')
    )
  )
$post
EOT
, "INSERT INTO `emp` (`name`,`sal`,`mgr`,`dept`) SELECT DISTINCT 'HENRY',T1.`sal`,'LEE','TOY' FROM `emp` T1 WHERE T1.`name`='LEWIS'");


# Deletion queries

PhpTest::test('qbe_convert-Company.34', 'Delete all information about employees in the Toy Department', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'D.',
      'dept' => 'TOY'
    )
  )
$post
EOT
, "DELETE FROM T1 USING `emp` T1 WHERE T1.`dept`='TOY'");

PhpTest::test('qbe_convert-Company.35', 'Delete all employees who work in departments that sell pens', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'D.',
      'dept' => '_D1'
    ),
    'sales' => array(
      'dept' => '_D1',
      'item' => 'PEN'
    )
  )
$post
EOT
, "DELETE FROM T1 USING `emp` T1 JOIN `sales` T2 ON T1.`dept`=T2.`dept` AND T2.`item`='PEN'");


# Updating queries

PhpTest::test('qbe_convert-Company.36', 'Update Henry\'s salary to $50 000', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'U.',
      'name' => 'HENRY',
      'sal'  => 50000
    )
  )
$post
EOT
, "UPDATE `emp` T1 SET T1.`sal`=50000 WHERE T1.`name`='HENRY'");

PhpTest::test('qbe_convert-Company.37', 'Raise the salaries of the employees in the Toy Department by ten percent', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => 'U.',
      # Please note the equi-join condition on the key field is needed.
      'name' => array('_N1', '_N1'),
      # Note that parenthesis around the expression are not needed.
      'sal'  => array('1.1 * _S1', '_S1'),
      # Note how the empty string as a first element would be ambiguous.
      'dept' => array(1 => 'TOY')
    )
  )
$post
EOT
# TODO: this query can be optimized by avoiding the self-join
, "UPDATE `emp` T1 JOIN `emp` T2 ON T1.`name`=T2.`name` AND T2.`dept`='TOY' SET T1.`sal`=1.1 * T2.`sal`");

PhpTest::test('qbe_convert-Company.38', 'Same as qbe_convert-Company.37 but with the order of the rows reversed', <<<EOT
$pre
  array(
    'emp' => array(
      '.rel' => array('', 'U.'),
      # Please note the equi-join condition on the key field is needed.
      'name' => array('_N1', '_N1'),
      # Note that parenthesis around the expression are not needed.
      'sal'  => array('_S1', '1.1 * _S1'),
      'dept' => 'TOY'
    )
  )
$post
EOT
# TODO: this query can be optimized by avoiding the self-join
, "UPDATE `emp` T1 JOIN `emp` T2 ON T1.`dept`='TOY' AND T1.`name`=T2.`name` SET T2.`sal`=1.1 * T1.`sal`");

echo '</pre>';

# End of file
# vim: set ts=2 sw=2 et:
?>
